import pandas as pd
import numpy as np
from sqlalchemy import create_engine

def do_excel():
    engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/pandas", echo=False)
    filepath = "../course_datas/c23_excel_vlookup/学生信息表.xlsx"
    df = pd.read_excel(filepath)
    # print(df.head())
    # 方法1：当数据表不存在时，每次覆盖整个表
    # 每次运行会drop table，新建表
    df.to_sql(name="student",con=engine,if_exists="replace")
    engine.execute("show create table student ").first()[1]
    print(engine.execute("show create table student").first()[1])

    print("数据量:",engine.execute("select count(1) from student").first())
    res = engine.execute("select * from student limit 5").fetchall()
    print("res:",res)

    # 方法2：当数据表存在时，每次新增数据
    df_new = df.loc[:4,:]
    print(df_new)
    # 当数据表存在时   dataframe 数据追加到 table
    df_new.to_sql(name="student",con=engine,if_exists="append")

    res = engine.execute("select * from student limit 6").fetchall()
    print("追加的数据量:",res)

    # 问题解决：先根据数据KEY删除旧数据
    print("index:",df_new.index)
    for id in df_new.index:
        ## 先删除要新增的数据
        delete_sql = f"delete from student where `index` = {id}"
        engine.execute(delete_sql)
    print("--------------------------------------")

    res = engine.execute("select * from student where `index` < 10").fetchall()
    print("res:",res)

    i = engine.execute("select count(1) from student ").first()
    print("数据总数:",i)















if __name__ == '__main__':
    do_excel()